-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: distinct含有主键字段

--step1：创建测试表与索引; expect：成功
drop table if exists t_ustore_dml_orderby_0049 cascade;
create table t_ustore_dml_orderby_0049(
     col_1 integer,
     col_2 bigint constraint cons1_t_rownum primary key,
     col_3 double precision,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 date,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);

--step2：创建序列并清空数据; expect：成功
drop sequence if exists t_ustore_dml_orderby_seq cascade;
create sequence t_ustore_dml_orderby_seq increment by 1 start with 10;
truncate table t_ustore_dml_orderby_0049;

--step3：插入数据; expect：成功
insert into t_ustore_dml_orderby_0049 values(1,t_ustore_dml_orderby_seq.nextval, 1+445.255,98*0.99,true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0049 values(1,t_ustore_dml_orderby_seq.nextval,1+445.255,98*0.99,true,lpad('abc','6','@'),lpad('abc','6','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0049 values(2,t_ustore_dml_orderby_seq.nextval, 1+445.255,98*0.99,false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0049 values(3,t_ustore_dml_orderby_seq.nextval, 1+445.255,98*0.99,false,lpad('abc','4','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0049 values(3,t_ustore_dml_orderby_seq.nextval, 1+445.255,98*0.99,false,lpad('abc','3','@'),lpad('abc','3','b'),rpad('abc','3','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0049 values(4,t_ustore_dml_orderby_seq.nextval, 1+445.255,98*0.99,true,lpad('abc','3','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
begin
for i in 6 .. 20 loop
insert into t_ustore_dml_orderby_0049 values(i,'1'||i,446.255,98*0.99,true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
end loop;
end;
/

--step4：distinct含有主键字段; expect：成功
explain select distinct col_2,col_3 from t_ustore_dml_orderby_0049;
explain select distinct col_2,col_3,col_1,col_16 from t_ustore_dml_orderby_0049;
explain select distinct count(*) from t_ustore_dml_orderby_0049;
explain select distinct col_3,col_2 from t_ustore_dml_orderby_0049;

--step5：清理环境; except：成功
drop sequence if exists t_ustore_dml_orderby_seq cascade;
drop table if exists t_ustore_dml_orderby_0049 cascade;